package com.hw.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiImport {

	private static final String EXCEL_XLS="xls";
	private static final String EXCEL_XLSX="xlsx";
	private static final int headCount=3;
	private static final int column=3;
	
	private Map<String,String> erroInfo;
	
	public List<User> readExcel(String fileName){
		
		List<User> list=new ArrayList<User>();
		erroInfo=new LinkedHashMap<String,String>();
		
		File file=new File(fileName);
		checkExcelVaild(file,erroInfo);
		
		FileInputStream fin=null;
		
		try {
			fin=new FileInputStream(file);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		
		Workbook wb = null;  
		
        if(fileName.endsWith(EXCEL_XLS)){  //Excel 2003 
            try {
				wb = new HSSFWorkbook(fin);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				erroInfo.put("文件提示", "请查看文件是否是正常的xls文件");
			}  
        }else if(fileName.endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
            try {
				wb = new XSSFWorkbook(fin);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				erroInfo.put("文件提示", "请查看文件是否是正常的xlsx文件");
				e.printStackTrace();
			}  
        }
        
        if(wb != null){  
            for(int sheetNum = 0;sheetNum < wb.getNumberOfSheets();sheetNum++){  
                //获得当前sheet工作表  
                Sheet sheet = wb.getSheetAt(sheetNum);  
                if(sheet == null){  
                    continue;  
                }  
                //获得当前sheet的开始行  
                int firstRowNum  = sheet.getFirstRowNum();  
                //获得当前sheet的结束行  
                int lastRowNum = sheet.getLastRowNum();  
                //循环除了第一行的所有行  
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){  
                    //获得当前行  
                    Row row = sheet.getRow(rowNum);  
                    if(row == null || rowNum<=headCount){  
                        continue;  
                    }  
                    //获得当前行的开始列  
                    int firstCellNum = row.getFirstCellNum();  
                    //获得当前行的列数  
                    int lastCellNum = row.getPhysicalNumberOfCells();  
                    String[] cells = new String[row.getPhysicalNumberOfCells()];  
                    
                    //循环当前行  
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){  
                    	Cell cell = row.getCell(cellNum);  
                    	cells[cellNum]=getCellValue(cell);
                    }
                    
                    User user=new User();
                    
                    user.setName(cells[0]);  
                    user.setSex(cells[1]);  
                    user.setAge(Integer.valueOf(cells[2]));  
                    list.add(user);
                     
                }  
            }  
        } 
        
        try {
			fin.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
        return list;
		
	}
	
	public static void checkExcelVaild(File file,Map<String, String> map){  
        if(!file.exists()){  
//            throw new Exception("文件不存在");
        	map.put("文件提示", file.getName()+"文件不存在");
        	
        }  
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
//            throw new Exception("文件不是Excel");  
        	map.put("文件格式提示", "文件不是Excel");
        }  
    } 
	
	 public static String getCellValue(Cell cell){  
	        String cellValue = "";  
	        if(cell == null){  
	            return cellValue;  
	        }  
	        //把数字当成String来读，避免出现1读成1.0的情况  
	        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){  
	            cell.setCellType(Cell.CELL_TYPE_STRING);  
	        }  
	        //判断数据的类型  
	        switch (cell.getCellType()){  
	            case Cell.CELL_TYPE_NUMERIC: //数字  
	                cellValue = String.valueOf(cell.getNumericCellValue());  
	                break;  
	            case Cell.CELL_TYPE_STRING: //字符串  
	                cellValue = String.valueOf(cell.getStringCellValue());  
	                break;  
	            case Cell.CELL_TYPE_BOOLEAN: //Boolean  
	                cellValue = String.valueOf(cell.getBooleanCellValue());  
	                break;  
	            case Cell.CELL_TYPE_FORMULA: //公式  
	                cellValue = String.valueOf(cell.getCellFormula());  
	                break;  
	            case Cell.CELL_TYPE_BLANK: //空值   
	                cellValue = "";  
	                break;  
	            case Cell.CELL_TYPE_ERROR: //故障  
	            	
	                cellValue = "非法字符";  
	                break;  
	            default:  
	                cellValue = "未知类型";  
	                break;  
	        }  
	        return cellValue;  
	    } 

}
